library(tidyverse)  # ggplot(), %>%, mutate(), and friends 
library(broom)
library(MatchIt)  # Match things
library(Rcpp)
library(MASS)
library(modelsummary)
library(IRdisplay)
library(haven)
library(sjmisc)
library(dplyr)


# set directory

getwd()


##################################################################################################
##################################################################################################
##################################################################################################
##########################################   1991   ##############################################
##################################################################################################
##################################################################################################
##################################################################################################


################ offense segment
offense91 <- readRDS("./nibrs_1991_2020_offense_segment_rds/nibrs_offense_segment_1991.rds")
offense91 <- filter(offense91, ucr_offense_code %in% c('justifiable homicide', 
                                                       'murder/nonnegligent manslaughter', 
                                                       'negligent manslaughter')) # filter offense
################ administrative segment
adm91 <- readRDS("./nibrs_1991_2020_administrative_segment_rds/nibrs_administrative_segment_1991.rds")
################ victim segment
vic91 <- readRDS("./nibrs_1991_2020_victim_segment_rds/nibrs_victim_segment_1991.rds")
vic91 <- filter(vic91, ucr_offense_code_1 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_2 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_3 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_4 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_5 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_6 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_7 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_8 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_9 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_10 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter'))
################ batch header
batch91 <- readRDS("./nibrs_batch_headers_rds/nibrs_batch_header_1991.rds")

########## arrestee segment
arrestee91 <- readRDS("./nibrs_1991_2020_arrestee_segment_rds/nibrs_arrestee_segment_1991.rds")

################ merge
merge1_91 <- merge(x=vic91, y=offense91, by="unique_incident_id", all.x=TRUE) # merge victim (index) and offense
batch91 <- rename(batch91, ori.x=ori) #rename batch for facilitating merging
merge2_91 <- merge(x=merge1_91, y=batch91, by="ori.x", all.x=TRUE) # merge previous, and batch
merge_final_91 <- merge(x=merge2_91, y=adm91, by="unique_incident_id", all.x=TRUE)# merge previous, and administrative
merge_final_91 <- merge(x=merge_final_91, y=arrestee91, by="unique_incident_id", all.x=TRUE)
#create Solved variable
merge_final_91$Solved <- ifelse(merge_final_91$total_arrestee_segments>0 | merge_final_91$cleared_exceptionally!="not applicable", 1, 0)
# remove to free up space in memory
rm(adm91, batch91, arrestee91)


##################################################################################################
##################################################################################################
##################################################################################################
##########################################   1992   ##############################################
##################################################################################################
##################################################################################################
##################################################################################################


################ offense segment
offense92 <- readRDS("./nibrs_1991_2020_offense_segment_rds/nibrs_offense_segment_1992.rds")
offense92 <- filter(offense92, ucr_offense_code %in% c('justifiable homicide', 
                                                       'murder/nonnegligent manslaughter', 
                                                       'negligent manslaughter')) # filter offense
################ administrative segment
adm92 <- readRDS("./nibrs_1991_2020_administrative_segment_rds/nibrs_administrative_segment_1992.rds")
################ victim segment
vic92 <- readRDS("./nibrs_1991_2020_victim_segment_rds/nibrs_victim_segment_1992.rds")
vic92 <- filter(vic92, ucr_offense_code_1 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_2 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_3 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_4 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_5 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_6 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_7 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_8 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_9 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_10 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter'))
################ batch header
batch92 <- readRDS("./nibrs_batch_headers_rds/nibrs_batch_header_1992.rds")

########## arrestee segment
arrestee92 <- readRDS("./nibrs_1991_2020_arrestee_segment_rds/nibrs_arrestee_segment_1992.rds")

################ merge
merge1_92 <- merge(x=vic92, y=offense92, by="unique_incident_id", all.x=TRUE) # merge victim (index) and offense
batch92 <- rename(batch92, ori.x=ori) #rename batch for facilitating merging
merge2_92 <- merge(x=merge1_92, y=batch92, by="ori.x", all.x=TRUE) # merge previous, and batch
merge_final_92 <- merge(x=merge2_92, y=adm92, by="unique_incident_id", all.x=TRUE) # merge previous, and administrative
merge_final_92 <- merge(x=merge_final_92, y=arrestee92, by="unique_incident_id", all.x=TRUE)
#create Solved variable
merge_final_92$Solved <- ifelse(merge_final_92$total_arrestee_segments>0 | merge_final_92$cleared_exceptionally!="not applicable", 1, 0)
# remove to free up space in memory
rm(adm92, batch92, arrestee92)


##################################################################################################
##################################################################################################
##################################################################################################
##########################################   1993   ##############################################
##################################################################################################
##################################################################################################
##################################################################################################


################ offense segment
offense93 <- readRDS("./nibrs_1991_2020_offense_segment_rds/nibrs_offense_segment_1993.rds")
offense93 <- filter(offense93, ucr_offense_code %in% c('justifiable homicide', 
                                                       'murder/nonnegligent manslaughter', 
                                                       'negligent manslaughter')) # filter offense
################ administrative segment
adm93 <- readRDS("./nibrs_1991_2020_administrative_segment_rds/nibrs_administrative_segment_1993.rds")
################ victim segment
vic93 <- readRDS("./nibrs_1991_2020_victim_segment_rds/nibrs_victim_segment_1993.rds")
vic93 <- filter(vic93, ucr_offense_code_1 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_2 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_3 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_3 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_4 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_5 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_6 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_7 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_8 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_9 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_10 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter'))
################ batch header
batch93 <- readRDS("./nibrs_batch_headers_rds/nibrs_batch_header_1993.rds")

########## arrestee segment
arrestee93 <- readRDS("./nibrs_1991_2020_arrestee_segment_rds/nibrs_arrestee_segment_1993.rds")


################ merge
merge1_93 <- merge(x=vic93, y=offense93, by="unique_incident_id", all.x=TRUE) # merge victim (index) and offense
batch93 <- rename(batch93, ori.x=ori) #rename batch for facilitating merging
merge2_93 <- merge(x=merge1_93, y=batch93, by="ori.x", all.x=TRUE) # merge previous, and batch
merge_final_93 <- merge(x=merge2_93, y=adm93, by="unique_incident_id", all.x=TRUE) # merge previous, and administrative
merge_final_93 <- merge(x=merge_final_93, y=arrestee93, by="unique_incident_id", all.x=TRUE)
#create Solved variable
merge_final_93$Solved <- ifelse(merge_final_93$total_arrestee_segments>0 | merge_final_93$cleared_exceptionally!="not applicable", 1, 0)
# remove to free up space in memory
rm(adm93, batch93, arrestee93)

##################################################################################################
##################################################################################################
##################################################################################################
##########################################   1994   ##############################################
##################################################################################################
##################################################################################################
##################################################################################################


################ offense segment
offense94 <- readRDS("./nibrs_1991_2020_offense_segment_rds/nibrs_offense_segment_1994.rds")
offense94 <- filter(offense94, ucr_offense_code %in% c('justifiable homicide', 
                                                       'murder/nonnegligent manslaughter', 
                                                       'negligent manslaughter')) # filter offense
################ administrative segment
adm94 <- readRDS("./nibrs_1991_2020_administrative_segment_rds/nibrs_administrative_segment_1994.rds")
################ victim segment
vic94 <- readRDS("./nibrs_1991_2020_victim_segment_rds/nibrs_victim_segment_1994.rds")
vic94 <- filter(vic94, ucr_offense_code_1 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_2 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_3 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_3 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_4 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_5 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_6 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_7 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_8 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_9 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_10 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter'))
################ batch header
batch94 <- readRDS("./nibrs_batch_headers_rds/nibrs_batch_header_1994.rds")

########## arrestee segment
arrestee94 <- readRDS("./nibrs_1991_2020_arrestee_segment_rds/nibrs_arrestee_segment_1994.rds")

################ merge
merge1_94 <- merge(x=vic94, y=offense94, by="unique_incident_id", all.x=TRUE) # merge victim (index) and offense
batch94 <- rename(batch94, ori.x=ori) #rename batch for facilitating merging
merge2_94 <- merge(x=merge1_94, y=batch94, by="ori.x", all.x=TRUE) # merge previous, and batch
merge_final_94 <- merge(x=merge2_94, y=adm94, by="unique_incident_id", all.x=TRUE) # merge previous, and administrative
merge_final_94 <- merge(x=merge_final_94, y=arrestee94, by="unique_incident_id", all.x=TRUE)
#create Solved variable
merge_final_94$Solved <- ifelse(merge_final_94$total_arrestee_segments>0 | merge_final_94$cleared_exceptionally!="not applicable", 1, 0)
# remove to free up space in memory
rm(adm94, batch94, arrestee94)



##################################################################################################
##################################################################################################
##################################################################################################
##########################################   1995   ##############################################
##################################################################################################
##################################################################################################
##################################################################################################


################ offense segment
offense95 <- readRDS("./nibrs_1991_2020_offense_segment_rds/nibrs_offense_segment_1995.rds")
offense95 <- filter(offense95, ucr_offense_code %in% c('justifiable homicide', 
                                                       'murder/nonnegligent manslaughter', 
                                                       'negligent manslaughter')) # filter offense
################ administrative segment
adm95 <- readRDS("./nibrs_1991_2020_administrative_segment_rds/nibrs_administrative_segment_1995.rds")
################ victim segment
vic95 <- readRDS("./nibrs_1991_2020_victim_segment_rds/nibrs_victim_segment_1995.rds")
vic95 <- filter(vic95, ucr_offense_code_1 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                         ucr_offense_code_2 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                         ucr_offense_code_3 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_3 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_4 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_5 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_6 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_7 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_8 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_9 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_10 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter'))
################ batch header
batch95 <- readRDS("./nibrs_batch_headers_rds/nibrs_batch_header_1995.rds")

########## arrestee segment
arrestee95 <- readRDS("./nibrs_1991_2020_arrestee_segment_rds/nibrs_arrestee_segment_1995.rds")

################ merge
merge1_95 <- merge(x=vic95, y=offense95, by="unique_incident_id", all.x=TRUE) # merge victim (index) and offense
batch95 <- rename(batch95, ori.x=ori) #rename batch for facilitating merging
merge2_95 <- merge(x=merge1_95, y=batch95, by="ori.x", all.x=TRUE) # merge previous, and batch
merge_final_95 <- merge(x=merge2_95, y=adm95, by="unique_incident_id", all.x=TRUE) # merge previous, and administrative
merge_final_95 <- merge(x=merge_final_95, y=arrestee95, by="unique_incident_id", all.x=TRUE)
#create Solved variable
merge_final_95$Solved <- ifelse(merge_final_95$total_arrestee_segments>0 | merge_final_95$cleared_exceptionally!="not applicable", 1, 0)
# remove to free up space in memory
rm(adm95, batch95, arrestee95)


##################################################################################################
##################################################################################################
##################################################################################################
##########################################   1996   ##############################################
##################################################################################################
##################################################################################################
##################################################################################################


################ offense segment
offense96 <- readRDS("./nibrs_1991_2020_offense_segment_rds/nibrs_offense_segment_1996.rds")
offense96 <- filter(offense96, ucr_offense_code %in% c('justifiable homicide', 
                                                       'murder/nonnegligent manslaughter', 
                                                       'negligent manslaughter')) # filter offense
################ administrative segment
adm96 <- readRDS("./nibrs_1991_2020_administrative_segment_rds/nibrs_administrative_segment_1996.rds")
################ victim segment
vic96 <- readRDS("./nibrs_1991_2020_victim_segment_rds/nibrs_victim_segment_1996.rds")
vic96 <- filter(vic96, ucr_offense_code_1 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_2 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_3 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_3 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_4 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_5 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_6 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_7 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_8 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_9 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_10 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter'))
################ batch header
batch96 <- readRDS("./nibrs_batch_headers_rds/nibrs_batch_header_1996.rds")
########## arrestee segment
arrestee96 <- readRDS("./nibrs_1991_2020_arrestee_segment_rds/nibrs_arrestee_segment_1996.rds")

################ merge
merge1_96 <- merge(x=vic96, y=offense96, by="unique_incident_id", all.x=TRUE) # merge victim (index) and offense
batch96 <- rename(batch96, ori.x=ori) #rename batch for facilitating merging
merge2_96 <- merge(x=merge1_96, y=batch96, by="ori.x", all.x=TRUE) # merge previous, and batch
merge_final_96 <- merge(x=merge2_96, y=adm96, by="unique_incident_id", all.x=TRUE) # merge previous, and administrative
merge_final_96 <- merge(x=merge_final_96, y=arrestee96, by="unique_incident_id", all.x=TRUE)
#create Solved variable
merge_final_96$Solved <- ifelse(merge_final_96$total_arrestee_segments>0 | merge_final_96$cleared_exceptionally!="not applicable", 1, 0)
# remove to free up space in memory
rm(adm96, batch96, arrestee96)

##################################################################################################
##################################################################################################
##################################################################################################
##########################################   1997   ##############################################
##################################################################################################
##################################################################################################
##################################################################################################


################ offense segment
offense97 <- readRDS("./nibrs_1991_2020_offense_segment_rds/nibrs_offense_segment_1997.rds")
offense97 <- filter(offense97, ucr_offense_code %in% c('justifiable homicide', 
                                                       'murder/nonnegligent manslaughter', 
                                                       'negligent manslaughter')) # filter offense
################ administrative segment
adm97 <- readRDS("./nibrs_1991_2020_administrative_segment_rds/nibrs_administrative_segment_1997.rds")
################ victim segment
vic97 <- readRDS("./nibrs_1991_2020_victim_segment_rds/nibrs_victim_segment_1997.rds")
vic97 <- filter(vic97, ucr_offense_code_1 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_2 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_3 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_3 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_4 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_5 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_6 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_7 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_8 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_9 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_10 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter'))
################ batch header
batch97 <- readRDS("./nibrs_batch_headers_rds/nibrs_batch_header_1997.rds")

########## arrestee segment
arrestee97 <- readRDS("./nibrs_1991_2020_arrestee_segment_rds/nibrs_arrestee_segment_1997.rds")

################ merge
merge1_97 <- merge(x=vic97, y=offense97, by="unique_incident_id", all.x=TRUE) # merge victim (index) and offense
batch97 <- rename(batch97, ori.x=ori) #rename batch for facilitating merging
merge2_97 <- merge(x=merge1_97, y=batch97, by="ori.x", all.x=TRUE) # merge previous, and batch
merge_final_97 <- merge(x=merge2_97, y=adm97, by="unique_incident_id", all.x=TRUE) # merge previous, and administrative
merge_final_97 <- merge(x=merge_final_97, y=arrestee97, by="unique_incident_id", all.x=TRUE)
#create Solved variable
merge_final_97$Solved <- ifelse(merge_final_97$total_arrestee_segments>0 | merge_final_97$cleared_exceptionally!="not applicable", 1, 0)
# remove to free up space in memory
rm(adm97, batch97, arrestee97)


##################################################################################################
##################################################################################################
##################################################################################################
##########################################   1998   ##############################################
##################################################################################################
##################################################################################################
##################################################################################################


################ offense segment
offense98 <- readRDS("./nibrs_1991_2020_offense_segment_rds/nibrs_offense_segment_1998.rds")
offense98 <- filter(offense98, ucr_offense_code %in% c('justifiable homicide', 
                                                       'murder/nonnegligent manslaughter', 
                                                       'negligent manslaughter')) # filter offense
################ administrative segment
adm98 <- readRDS("./nibrs_1991_2020_administrative_segment_rds/nibrs_administrative_segment_1998.rds")
################ victim segment
vic98 <- readRDS("./nibrs_1991_2020_victim_segment_rds/nibrs_victim_segment_1998.rds")
vic98 <- filter(vic98, ucr_offense_code_1 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_2 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_3 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_3 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_4 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_5 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_6 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_7 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_8 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_9 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_10 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter'))
################ batch header
batch98 <- readRDS("./nibrs_batch_headers_rds/nibrs_batch_header_1998.rds")

########## arrestee segment
arrestee98 <- readRDS("./nibrs_1991_2020_arrestee_segment_rds/nibrs_arrestee_segment_1998.rds")

################ merge
merge1_98 <- merge(x=vic98, y=offense98, by="unique_incident_id", all.x=TRUE) # merge victim (index) and offense
batch98 <- rename(batch98, ori.x=ori) #rename batch for facilitating merging
merge2_98 <- merge(x=merge1_98, y=batch98, by="ori.x", all.x=TRUE) # merge previous, and batch
merge_final_98 <- merge(x=merge2_98, y=adm98, by="unique_incident_id", all.x=TRUE) # merge previous, and administrative
merge_final_98 <- merge(x=merge_final_98, y=arrestee98, by="unique_incident_id", all.x=TRUE)
#create Solved variable
merge_final_98$Solved <- ifelse(merge_final_98$total_arrestee_segments>0 | merge_final_98$cleared_exceptionally!="not applicable", 1, 0)
# remove to free up space in memory
rm(adm98, batch98, arrestee98)



##################################################################################################
##################################################################################################
##################################################################################################
##########################################   1999   ##############################################
##################################################################################################
##################################################################################################
##################################################################################################


################ offense segment
offense99 <- readRDS("./nibrs_1991_2020_offense_segment_rds/nibrs_offense_segment_1999.rds")
offense99 <- filter(offense99, ucr_offense_code %in% c('justifiable homicide', 
                                                       'murder/nonnegligent manslaughter', 
                                                       'negligent manslaughter')) # filter offense
################ administrative segment
adm99 <- readRDS("./nibrs_1991_2020_administrative_segment_rds/nibrs_administrative_segment_1999.rds")
################ victim segment
vic99 <- readRDS("./nibrs_1991_2020_victim_segment_rds/nibrs_victim_segment_1999.rds")
vic99 <- filter(vic99, ucr_offense_code_1 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_2 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_3 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_3 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_4 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_5 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_6 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_7 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_8 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_9 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_10 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter'))
################ batch header
batch99 <- readRDS("./nibrs_batch_headers_rds/nibrs_batch_header_1999.rds")
########## arrestee segment
arrestee99 <- readRDS("./nibrs_1991_2020_arrestee_segment_rds/nibrs_arrestee_segment_1999.rds")

################ merge
merge1_99 <- merge(x=vic99, y=offense99, by="unique_incident_id", all.x=TRUE) # merge victim (index) and offense
batch99 <- rename(batch99, ori.x=ori) #rename batch for facilitating merging
merge2_99 <- merge(x=merge1_99, y=batch99, by="ori.x", all.x=TRUE) # merge previous, and batch
merge_final_99 <- merge(x=merge2_99, y=adm99, by="unique_incident_id", all.x=TRUE) # merge previous, and administrative
merge_final_99 <- merge(x=merge_final_99, y=arrestee99, by="unique_incident_id", all.x=TRUE)
#create Solved variable
merge_final_99$Solved <- ifelse(merge_final_99$total_arrestee_segments>0 | merge_final_99$cleared_exceptionally!="not applicable", 1, 0)
# remove to free up space in memory
rm(adm99, batch99, arrestee99)


##################################################################################################
##################################################################################################
##################################################################################################
##########################################   2000   ##############################################
##################################################################################################
##################################################################################################
##################################################################################################


################ offense segment
offense00 <- readRDS("./nibrs_1991_2020_offense_segment_rds/nibrs_offense_segment_2000.rds")
offense00 <- filter(offense00, ucr_offense_code %in% c('justifiable homicide', 
                                                       'murder/nonnegligent manslaughter', 
                                                       'negligent manslaughter')) # filter offense
################ administrative segment
adm00 <- readRDS("./nibrs_1991_2020_administrative_segment_rds/nibrs_administrative_segment_2000.rds")
################ victim segment
vic00 <- readRDS("./nibrs_1991_2020_victim_segment_rds/nibrs_victim_segment_2000.rds")
vic00 <- filter(vic00, ucr_offense_code_1 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_2 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_3 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_3 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_4 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_5 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_6 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_7 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_8 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_9 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_10 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter'))
################ batch header
batch00 <- readRDS("./nibrs_batch_headers_rds/nibrs_batch_header_2000.rds")

########## arrestee segment
arrestee00 <- readRDS("./nibrs_1991_2020_arrestee_segment_rds/nibrs_arrestee_segment_2000.rds")

################ merge
merge1_00 <- merge(x=vic00, y=offense00, by="unique_incident_id", all.x=TRUE) # merge victim (index) and offense
batch00 <- rename(batch00, ori.x=ori) #rename batch for facilitating merging
merge2_00 <- merge(x=merge1_00, y=batch00, by="ori.x", all.x=TRUE) # merge previous, and batch
merge_final_00 <- merge(x=merge2_00, y=adm00, by="unique_incident_id", all.x=TRUE) # merge previous, and administrative
merge_final_00 <- merge(x=merge_final_00, y=arrestee00, by="unique_incident_id", all.x=TRUE)
#create Solved variable
merge_final_00$Solved <- ifelse(merge_final_00$total_arrestee_segments>0 | merge_final_00$cleared_exceptionally!="not applicable", 1, 0)
# remove to free up space in memory
rm(adm00, batch00, arrestee00)


##################################################################################################
##################################################################################################
##################################################################################################
##########################################   2001   ##############################################
##################################################################################################
##################################################################################################
##################################################################################################


################ offense segment
offense01 <- readRDS("./nibrs_1991_2020_offense_segment_rds/nibrs_offense_segment_2001.rds")
offense01 <- filter(offense01, ucr_offense_code %in% c('justifiable homicide', 
                                                       'murder/nonnegligent manslaughter', 
                                                       'negligent manslaughter')) # filter offense
################ administrative segment
adm01 <- readRDS("./nibrs_1991_2020_administrative_segment_rds/nibrs_administrative_segment_2001.rds")
################ victim segment
vic01 <- readRDS("./nibrs_1991_2020_victim_segment_rds/nibrs_victim_segment_2001.rds")
vic01 <- filter(vic01, ucr_offense_code_1 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_2 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_3 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_3 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_4 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_5 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_6 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_7 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_8 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_9 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_10 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter'))
################ batch header
batch01 <- readRDS("./nibrs_batch_headers_rds/nibrs_batch_header_2001.rds")
########## arrestee segment
arrestee01 <- readRDS("./nibrs_1991_2020_arrestee_segment_rds/nibrs_arrestee_segment_2001.rds")
################ merge
merge1_01 <- merge(x=vic01, y=offense01, by="unique_incident_id", all.x=TRUE) # merge victim (index) and offense
batch01 <- rename(batch01, ori.x=ori) #rename batch for facilitating merging
merge2_01 <- merge(x=merge1_01, y=batch01, by="ori.x", all.x=TRUE) # merge previous, and batch
merge_final_01 <- merge(x=merge2_01, y=adm01, by="unique_incident_id", all.x=TRUE) # merge previous, and administrative
merge_final_01 <- merge(x=merge_final_01, y=arrestee01, by="unique_incident_id", all.x=TRUE)
#create Solved variable
merge_final_01$Solved <- ifelse(merge_final_01$total_arrestee_segments>0 | merge_final_01$cleared_exceptionally!="not applicable", 1, 0)
# remove to free up space in memory
rm(adm01, batch01, arrestee01)


##################################################################################################
##################################################################################################
##################################################################################################
##########################################   2002   ##############################################
##################################################################################################
##################################################################################################
##################################################################################################


################ offense segment
offense02 <- readRDS("./nibrs_1991_2020_offense_segment_rds/nibrs_offense_segment_2002.rds")
offense02 <- filter(offense02, ucr_offense_code %in% c('justifiable homicide', 
                                                       'murder/nonnegligent manslaughter', 
                                                       'negligent manslaughter')) # filter offense
################ administrative segment
adm02 <- readRDS("./nibrs_1991_2020_administrative_segment_rds/nibrs_administrative_segment_2002.rds")
################ victim segment
vic02 <- readRDS("./nibrs_1991_2020_victim_segment_rds/nibrs_victim_segment_2002.rds")
vic02 <- filter(vic02, ucr_offense_code_1 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_2 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_3 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_3 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_4 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_5 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_6 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_7 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_8 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_9 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_10 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter'))
################ batch header
batch02 <- readRDS("./nibrs_batch_headers_rds/nibrs_batch_header_2002.rds")
########## arrestee segment
arrestee02 <- readRDS("./nibrs_1991_2020_arrestee_segment_rds/nibrs_arrestee_segment_2002.rds")
################ merge
merge1_02 <- merge(x=vic02, y=offense02, by="unique_incident_id", all.x=TRUE) # merge victim (index) and offense
batch02 <- rename(batch02, ori.x=ori) #rename batch for facilitating merging
merge2_02 <- merge(x=merge1_02, y=batch02, by="ori.x", all.x=TRUE) # merge previous, and batch
merge_final_02 <- merge(x=merge2_02, y=adm02, by="unique_incident_id", all.x=TRUE) # merge previous, and administrative
merge_final_02 <- merge(x=merge_final_02, y=arrestee02, by="unique_incident_id", all.x=TRUE)
#create Solved variable
merge_final_02$Solved <- ifelse(merge_final_02$total_arrestee_segments>0 | merge_final_02$cleared_exceptionally!="not applicable", 1, 0)
# remove to free up space in memory
rm(adm02, batch02, arrestee02)


##################################################################################################
##################################################################################################
##################################################################################################
##########################################   2003   ##############################################
##################################################################################################
##################################################################################################
##################################################################################################


################ offense segment
offense03 <- readRDS("./nibrs_1991_2020_offense_segment_rds/nibrs_offense_segment_2003.rds")
offense03 <- filter(offense03, ucr_offense_code %in% c('justifiable homicide', 
                                                       'murder/nonnegligent manslaughter', 
                                                       'negligent manslaughter')) # filter offense
################ administrative segment
adm03 <- readRDS("./nibrs_1991_2020_administrative_segment_rds/nibrs_administrative_segment_2003.rds")
################ victim segment
vic03 <- readRDS("./nibrs_1991_2020_victim_segment_rds/nibrs_victim_segment_2003.rds")
vic03 <- filter(vic03, ucr_offense_code_1 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_2 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_3 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_3 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_4 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_5 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_6 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_7 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_8 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_9 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_10 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter'))
################ batch header
batch03 <- readRDS("./nibrs_batch_headers_rds/nibrs_batch_header_2003.rds")
########## arrestee segment
arrestee03 <- readRDS("./nibrs_1991_2020_arrestee_segment_rds/nibrs_arrestee_segment_2003.rds")
################ merge
merge1_03 <- merge(x=vic03, y=offense03, by="unique_incident_id", all.x=TRUE) # merge victim (index) and offense
batch03 <- rename(batch03, ori.x=ori) #rename batch for facilitating merging
merge2_03 <- merge(x=merge1_03, y=batch03, by="ori.x", all.x=TRUE) # merge previous, and batch
merge_final_03 <- merge(x=merge2_03, y=adm03, by="unique_incident_id", all.x=TRUE) # merge previous, and administrative
merge_final_03 <- merge(x=merge_final_03, y=arrestee03, by="unique_incident_id", all.x=TRUE)
#create Solved variable
merge_final_03$Solved <- ifelse(merge_final_03$total_arrestee_segments>0 | merge_final_03$cleared_exceptionally!="not applicable", 1, 0)
# remove to free up space in memory
rm(adm03, batch03, arrestee03)


##################################################################################################
##################################################################################################
##################################################################################################
##########################################   2004   ##############################################
##################################################################################################
##################################################################################################
##################################################################################################


################ offense segment
offense04 <- readRDS("./nibrs_1991_2020_offense_segment_rds/nibrs_offense_segment_2004.rds")
offense04 <- filter(offense04, ucr_offense_code %in% c('justifiable homicide', 
                                                       'murder/nonnegligent manslaughter', 
                                                       'negligent manslaughter')) # filter offense
################ administrative segment
adm04 <- readRDS("./nibrs_1991_2020_administrative_segment_rds/nibrs_administrative_segment_2004.rds")
################ victim segment
vic04 <- readRDS("./nibrs_1991_2020_victim_segment_rds/nibrs_victim_segment_2004.rds")
vic04 <- filter(vic04, ucr_offense_code_1 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_2 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_3 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_3 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_4 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_5 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_6 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_7 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_8 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_9 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_10 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter'))
################ batch header
batch04 <- readRDS("./nibrs_batch_headers_rds/nibrs_batch_header_2004.rds")
########## arrestee segment
arrestee04 <- readRDS("./nibrs_1991_2020_arrestee_segment_rds/nibrs_arrestee_segment_2004.rds")
################ merge
merge1_04 <- merge(x=vic04, y=offense04, by="unique_incident_id", all.x=TRUE) # merge victim (index) and offense
batch04 <- rename(batch04, ori.x=ori) #rename batch for facilitating merging
merge2_04 <- merge(x=merge1_04, y=batch04, by="ori.x", all.x=TRUE) # merge previous, and batch
merge_final_04 <- merge(x=merge2_04, y=adm04, by="unique_incident_id", all.x=TRUE) # merge previous, and administrative
merge_final_04 <- merge(x=merge_final_04, y=arrestee04, by="unique_incident_id", all.x=TRUE)
#create Solved variable
merge_final_04$Solved <- ifelse(merge_final_04$total_arrestee_segments>0 | merge_final_04$cleared_exceptionally!="not applicable", 1, 0)
# remove to free up space in memory
rm(adm04, batch04, arrestee04)

##################################################################################################
##################################################################################################
##################################################################################################
##########################################   2005   ##############################################
##################################################################################################
##################################################################################################
##################################################################################################


################ offense segment
offense05 <- readRDS("./nibrs_1991_2020_offense_segment_rds/nibrs_offense_segment_2005.rds")
offense05 <- filter(offense05, ucr_offense_code %in% c('justifiable homicide', 
                                                       'murder/nonnegligent manslaughter', 
                                                       'negligent manslaughter')) # filter offense
################ administrative segment
adm05 <- readRDS("./nibrs_1991_2020_administrative_segment_rds/nibrs_administrative_segment_2005.rds")
################ victim segment
vic05 <- readRDS("./nibrs_1991_2020_victim_segment_rds/nibrs_victim_segment_2005.rds")
vic05 <- filter(vic05, ucr_offense_code_1 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_2 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_3 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_3 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_4 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_5 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_6 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_7 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_8 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_9 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_10 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter'))
################ batch header
batch05 <- readRDS("./nibrs_batch_headers_rds/nibrs_batch_header_2005.rds")
########## arrestee segment
arrestee05 <- readRDS("./nibrs_1991_2020_arrestee_segment_rds/nibrs_arrestee_segment_2005.rds")
################ merge
merge1_05 <- merge(x=vic05, y=offense05, by="unique_incident_id", all.x=TRUE) # merge victim (index) and offense
batch05 <- rename(batch05, ori.x=ori) #rename batch for facilitating merging
merge2_05 <- merge(x=merge1_05, y=batch05, by="ori.x", all.x=TRUE) # merge previous, and batch
merge_final_05 <- merge(x=merge2_05, y=adm05, by="unique_incident_id", all.x=TRUE) # merge previous, and administrative
merge_final_05 <- merge(x=merge_final_05, y=arrestee05, by="unique_incident_id", all.x=TRUE)
#create Solved variable
merge_final_05$Solved <- ifelse(merge_final_05$total_arrestee_segments>0 | merge_final_05$cleared_exceptionally!="not applicable", 1, 0)
# remove to free up space in memory
rm(adm05, batch05, arrestee05)


##################################################################################################
##################################################################################################
##################################################################################################
##########################################   2006   ##############################################
##################################################################################################
##################################################################################################
##################################################################################################


################ offense segment
offense06 <- readRDS("./nibrs_1991_2020_offense_segment_rds/nibrs_offense_segment_2006.rds")
offense06 <- filter(offense06, ucr_offense_code %in% c('justifiable homicide', 
                                                       'murder/nonnegligent manslaughter', 
                                                       'negligent manslaughter')) # filter offense
################ administrative segment
adm06 <- readRDS("./nibrs_1991_2020_administrative_segment_rds/nibrs_administrative_segment_2006.rds")
################ victim segment
vic06 <- readRDS("./nibrs_1991_2020_victim_segment_rds/nibrs_victim_segment_2006.rds")
vic06 <- filter(vic06, ucr_offense_code_1 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_2 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_3 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_3 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_4 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_5 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_6 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_7 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_8 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_9 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_10 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter'))
################ batch header
batch06 <- readRDS("./nibrs_batch_headers_rds/nibrs_batch_header_2006.rds")
########## arrestee segment
arrestee06 <- readRDS("./nibrs_1991_2020_arrestee_segment_rds/nibrs_arrestee_segment_2006.rds")
################ merge
merge1_06 <- merge(x=vic06, y=offense06, by="unique_incident_id", all.x=TRUE) # merge victim (index) and offense
batch06 <- rename(batch06, ori.x=ori) #rename batch for facilitating merging
merge2_06 <- merge(x=merge1_06, y=batch06, by="ori.x", all.x=TRUE) # merge previous, and batch
merge_final_06 <- merge(x=merge2_06, y=adm06, by="unique_incident_id", all.x=TRUE) # merge previous, and administrative
merge_final_06 <- merge(x=merge_final_06, y=arrestee06, by="unique_incident_id", all.x=TRUE)
#create Solved variable
merge_final_06$Solved <- ifelse(merge_final_06$total_arrestee_segments>0 | merge_final_06$cleared_exceptionally!="not applicable", 1, 0)
# remove to free up space in memory
rm(adm06, batch06, arrestee06)


##################################################################################################
##################################################################################################
##################################################################################################
##########################################   2007   ##############################################
##################################################################################################
##################################################################################################
##################################################################################################


################ offense segment
offense07 <- readRDS("./nibrs_1991_2020_offense_segment_rds/nibrs_offense_segment_2007.rds")
offense07 <- filter(offense07, ucr_offense_code %in% c('justifiable homicide', 
                                                       'murder/nonnegligent manslaughter', 
                                                       'negligent manslaughter')) # filter offense
################ administrative segment
adm07 <- readRDS("./nibrs_1991_2020_administrative_segment_rds/nibrs_administrative_segment_2007.rds")
################ victim segment
vic07 <- readRDS("./nibrs_1991_2020_victim_segment_rds/nibrs_victim_segment_2007.rds")
vic07 <- filter(vic07, ucr_offense_code_1 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_2 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_3 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_3 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_4 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_5 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_6 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_7 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_8 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_9 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_10 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter'))
################ batch header
batch07 <- readRDS("./nibrs_batch_headers_rds/nibrs_batch_header_2007.rds")
########## arrestee segment
arrestee07 <- readRDS("./nibrs_1991_2020_arrestee_segment_rds/nibrs_arrestee_segment_2007.rds")
################ merge
merge1_07 <- merge(x=vic07, y=offense07, by="unique_incident_id", all.x=TRUE) # merge victim (index) and offense
batch07 <- rename(batch07, ori.x=ori) #rename batch for facilitating merging
merge2_07 <- merge(x=merge1_07, y=batch07, by="ori.x", all.x=TRUE) # merge previous, and batch
merge_final_07 <- merge(x=merge2_07, y=adm07, by="unique_incident_id", all.x=TRUE) # merge previous, and administrative
merge_final_07 <- merge(x=merge_final_07, y=arrestee07, by="unique_incident_id", all.x=TRUE)
#create Solved variable
merge_final_07$Solved <- ifelse(merge_final_07$total_arrestee_segments>0 | merge_final_07$cleared_exceptionally!="not applicable", 1, 0)
# remove to free up space in memory
rm(adm07, batch07, arrestee07)


##################################################################################################
##################################################################################################
##################################################################################################
##########################################   2008   ##############################################
##################################################################################################
##################################################################################################
##################################################################################################


################ offense segment
offense08 <- readRDS("./nibrs_1991_2020_offense_segment_rds/nibrs_offense_segment_2008.rds")
offense08 <- filter(offense08, ucr_offense_code %in% c('justifiable homicide', 
                                                       'murder/nonnegligent manslaughter', 
                                                       'negligent manslaughter')) # filter offense
################ administrative segment
adm08 <- readRDS("./nibrs_1991_2020_administrative_segment_rds/nibrs_administrative_segment_2008.rds")
################ victim segment
vic08 <- readRDS("./nibrs_1991_2020_victim_segment_rds/nibrs_victim_segment_2008.rds")
vic08 <- filter(vic08, ucr_offense_code_1 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_2 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_3 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_3 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_4 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_5 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_6 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_7 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_8 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_9 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_10 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter'))
################ batch header
batch08 <- readRDS("./nibrs_batch_headers_rds/nibrs_batch_header_2008.rds")
########## arrestee segment
arrestee08 <- readRDS("./nibrs_1991_2020_arrestee_segment_rds/nibrs_arrestee_segment_2008.rds")
################ merge
merge1_08 <- merge(x=vic08, y=offense08, by="unique_incident_id", all.x=TRUE) # merge victim (index) and offense
batch08 <- rename(batch08, ori.x=ori) #rename batch for facilitating merging
merge2_08 <- merge(x=merge1_08, y=batch08, by="ori.x", all.x=TRUE) # merge previous, and batch
merge_final_08 <- merge(x=merge2_08, y=adm08, by="unique_incident_id", all.x=TRUE) # merge previous, and administrative
merge_final_08 <- merge(x=merge_final_08, y=arrestee08, by="unique_incident_id", all.x=TRUE)
#create Solved variable
merge_final_08$Solved <- ifelse(merge_final_08$total_arrestee_segments>0 | merge_final_08$cleared_exceptionally!="not applicable", 1, 0)
# remove to free up space in memory
rm(adm08, batch08, arrestee08)

##################################################################################################
##################################################################################################
##################################################################################################
##########################################   2009   ##############################################
##################################################################################################
##################################################################################################
##################################################################################################


################ offense segment
offense09 <- readRDS("./nibrs_1991_2020_offense_segment_rds/nibrs_offense_segment_2009.rds")
offense09 <- filter(offense09, ucr_offense_code %in% c('justifiable homicide', 
                                                       'murder/nonnegligent manslaughter', 
                                                       'negligent manslaughter')) # filter offense
################ administrative segment
adm09 <- readRDS("./nibrs_1991_2020_administrative_segment_rds/nibrs_administrative_segment_2009.rds")
################ victim segment
vic09 <- readRDS("./nibrs_1991_2020_victim_segment_rds/nibrs_victim_segment_2009.rds")
vic09 <- filter(vic09, ucr_offense_code_1 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_2 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_3 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_3 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_4 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_5 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_6 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_7 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_8 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_9 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_10 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter'))
################ batch header
batch09 <- readRDS("./nibrs_batch_headers_rds/nibrs_batch_header_2009.rds")
########## arrestee segment
arrestee09 <- readRDS("./nibrs_1991_2020_arrestee_segment_rds/nibrs_arrestee_segment_2009.rds")
################ merge
merge1_09 <- merge(x=vic09, y=offense09, by="unique_incident_id", all.x=TRUE) # merge victim (index) and offense
batch09 <- rename(batch09, ori.x=ori) #rename batch for facilitating merging
merge2_09 <- merge(x=merge1_09, y=batch09, by="ori.x", all.x=TRUE) # merge previous, and batch
merge_final_09 <- merge(x=merge2_09, y=adm09, by="unique_incident_id", all.x=TRUE) # merge previous, and administrative
merge_final_09 <- merge(x=merge_final_09, y=arrestee09, by="unique_incident_id", all.x=TRUE)
#create Solved variable
merge_final_09$Solved <- ifelse(merge_final_09$total_arrestee_segments>0 | merge_final_09$cleared_exceptionally!="not applicable", 1, 0)
# remove to free up space in memory
rm(adm09, batch09, arrestee09)

##################################################################################################
##################################################################################################
##################################################################################################
##########################################   2010   ##############################################
##################################################################################################
##################################################################################################
##################################################################################################


################ offense segment
offense10 <- readRDS("./nibrs_1991_2020_offense_segment_rds/nibrs_offense_segment_2010.rds")
offense10 <- filter(offense10, ucr_offense_code %in% c('justifiable homicide', 
                                                       'murder/nonnegligent manslaughter', 
                                                       'negligent manslaughter')) # filter offense
################ administrative segment
adm10 <- readRDS("./nibrs_1991_2020_administrative_segment_rds/nibrs_administrative_segment_2010.rds")
################ victim segment
vic10 <- readRDS("./nibrs_1991_2020_victim_segment_rds/nibrs_victim_segment_2010.rds")
vic10 <- filter(vic10, ucr_offense_code_1 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_2 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_3 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_3 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_4 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_5 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_6 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_7 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_8 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_9 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_10 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter'))
################ batch header
batch10 <- readRDS("./nibrs_batch_headers_rds/nibrs_batch_header_2010.rds")
########## arrestee segment
arrestee10 <- readRDS("./nibrs_1991_2020_arrestee_segment_rds/nibrs_arrestee_segment_2010.rds")
################ merge
merge1_10 <- merge(x=vic10, y=offense10, by="unique_incident_id", all.x=TRUE) # merge victim (index) and offense
batch10 <- rename(batch10, ori.x=ori) #rename batch for facilitating merging
merge2_10 <- merge(x=merge1_10, y=batch10, by="ori.x", all.x=TRUE) # merge previous, and batch
merge_final_10 <- merge(x=merge2_10, y=adm10, by="unique_incident_id", all.x=TRUE) # merge previous, and administrative
merge_final_10 <- merge(x=merge_final_10, y=arrestee10, by="unique_incident_id", all.x=TRUE)
#create Solved variable
merge_final_10$Solved <- ifelse(merge_final_10$total_arrestee_segments>0 | merge_final_10$cleared_exceptionally!="not applicable", 1, 0)
# remove to free up space in memory
rm(adm10, batch10, arrestee10)


##################################################################################################
##################################################################################################
##################################################################################################
##########################################   2011   ##############################################
##################################################################################################
##################################################################################################
##################################################################################################


################ offense segment
offense11 <- readRDS("./nibrs_1991_2020_offense_segment_rds/nibrs_offense_segment_2011.rds")
offense11 <- filter(offense11, ucr_offense_code %in% c('justifiable homicide', 
                                                       'murder/nonnegligent manslaughter', 
                                                       'negligent manslaughter')) # filter offense
################ administrative segment
adm11 <- readRDS("./nibrs_1991_2020_administrative_segment_rds/nibrs_administrative_segment_2011.rds")
################ victim segment
vic11 <- readRDS("./nibrs_1991_2020_victim_segment_rds/nibrs_victim_segment_2011.rds")
vic11 <- filter(vic11, ucr_offense_code_1 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_2 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_3 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_3 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_4 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_5 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_6 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_7 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_8 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_9 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_10 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter'))
################ batch header
batch11 <- readRDS("./nibrs_batch_headers_rds/nibrs_batch_header_2011.rds")
########## arrestee segment
arrestee11 <- readRDS("./nibrs_1991_2020_arrestee_segment_rds/nibrs_arrestee_segment_2011.rds")
################ merge
merge1_11 <- merge(x=vic11, y=offense11, by="unique_incident_id", all.x=TRUE) # merge victim (index) and offense
batch11 <- rename(batch11, ori.x=ori) #rename batch for facilitating merging
merge2_11 <- merge(x=merge1_11, y=batch11, by="ori.x", all.x=TRUE) # merge previous, and batch
merge_final_11 <- merge(x=merge2_11, y=adm11, by="unique_incident_id", all.x=TRUE) # merge previous, and administrative
merge_final_11 <- merge(x=merge_final_11, y=arrestee11, by="unique_incident_id", all.x=TRUE)
#create Solved variable
merge_final_11$Solved <- ifelse(merge_final_11$total_arrestee_segments>0 | merge_final_11$cleared_exceptionally!="not applicable", 1, 0)
# remove to free up space in memory
rm(adm11, batch11, arrestee11)


##################################################################################################
##################################################################################################
##################################################################################################
##########################################   2012   ##############################################
##################################################################################################
##################################################################################################
##################################################################################################


################ offense segment
offense12 <- readRDS("./nibrs_1991_2020_offense_segment_rds/nibrs_offense_segment_2012.rds")
offense12 <- filter(offense12, ucr_offense_code %in% c('justifiable homicide', 
                                                       'murder/nonnegligent manslaughter', 
                                                       'negligent manslaughter')) # filter offense
################ administrative segment
adm12 <- readRDS("./nibrs_1991_2020_administrative_segment_rds/nibrs_administrative_segment_2012.rds")
################ victim segment
vic12 <- readRDS("./nibrs_1991_2020_victim_segment_rds/nibrs_victim_segment_2012.rds")
vic12 <- filter(vic12, ucr_offense_code_1 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_2 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_3 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_3 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_4 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_5 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_6 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_7 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_8 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_9 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_10 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter'))
################ batch header
batch12 <- readRDS("./nibrs_batch_headers_rds/nibrs_batch_header_2012.rds")
########## arrestee segment
arrestee12 <- readRDS("./nibrs_1991_2020_arrestee_segment_rds/nibrs_arrestee_segment_2012.rds")
################ merge
merge1_12 <- merge(x=vic12, y=offense12, by="unique_incident_id", all.x=TRUE) # merge victim (index) and offense
batch12 <- rename(batch12, ori.x=ori) #rename batch for facilitating merging
merge2_12 <- merge(x=merge1_12, y=batch12, by="ori.x", all.x=TRUE) # merge previous, and batch
merge_final_12 <- merge(x=merge2_12, y=adm12, by="unique_incident_id", all.x=TRUE) # merge previous, and administrative
merge_final_12 <- merge(x=merge_final_12, y=arrestee12, by="unique_incident_id", all.x=TRUE)
#create Solved variable
merge_final_12$Solved <- ifelse(merge_final_12$total_arrestee_segments>0 | merge_final_12$cleared_exceptionally!="not applicable", 1, 0)
# remove to free up space in memory
rm(adm12, batch12, arrestee12)


##################################################################################################
##################################################################################################
##################################################################################################
##########################################   2013   ##############################################
##################################################################################################
##################################################################################################
##################################################################################################


################ offense segment
offense13 <- readRDS("./nibrs_1991_2020_offense_segment_rds/nibrs_offense_segment_2013.rds")
offense13 <- filter(offense13, ucr_offense_code %in% c('justifiable homicide', 
                                                       'murder/nonnegligent manslaughter', 
                                                       'negligent manslaughter')) # filter offense
################ administrative segment
adm13 <- readRDS("./nibrs_1991_2020_administrative_segment_rds/nibrs_administrative_segment_2013.rds")
################ victim segment
vic13 <- readRDS("./nibrs_1991_2020_victim_segment_rds/nibrs_victim_segment_2013.rds")
vic13 <- filter(vic13, ucr_offense_code_1 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_2 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_3 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_3 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_4 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_5 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_6 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_7 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_8 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_9 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_10 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter'))
################ batch header
batch13 <- readRDS("./nibrs_batch_headers_rds/nibrs_batch_header_2013.rds")
########## arrestee segment
arrestee13 <- readRDS("./nibrs_1991_2020_arrestee_segment_rds/nibrs_arrestee_segment_2013.rds")
################ merge
merge1_13 <- merge(x=vic13, y=offense13, by="unique_incident_id", all.x=TRUE) # merge victim (index) and offense
batch13 <- rename(batch13, ori.x=ori) #rename batch for facilitating merging
merge2_13 <- merge(x=merge1_13, y=batch13, by="ori.x", all.x=TRUE) # merge previous, and batch
merge_final_13 <- merge(x=merge2_13, y=adm13, by="unique_incident_id", all.x=TRUE) # merge previous, and administrative
merge_final_13 <- merge(x=merge_final_13, y=arrestee13, by="unique_incident_id", all.x=TRUE)
#create Solved variable
merge_final_13$Solved <- ifelse(merge_final_13$total_arrestee_segments>0 | merge_final_13$cleared_exceptionally!="not applicable", 1, 0)
# remove to free up space in memory
rm(adm13, batch13, arrestee13)


##################################################################################################
##################################################################################################
##################################################################################################
##########################################   2014   ##############################################
##################################################################################################
##################################################################################################
##################################################################################################


################ offense segment
offense14 <- readRDS("./nibrs_1991_2020_offense_segment_rds/nibrs_offense_segment_2014.rds")
offense14 <- filter(offense14, ucr_offense_code %in% c('justifiable homicide', 
                                                       'murder/nonnegligent manslaughter', 
                                                       'negligent manslaughter')) # filter offense
################ administrative segment
adm14 <- readRDS("./nibrs_1991_2020_administrative_segment_rds/nibrs_administrative_segment_2014.rds")
################ victim segment
vic14 <- readRDS("./nibrs_1991_2020_victim_segment_rds/nibrs_victim_segment_2014.rds")
vic14 <- filter(vic14, ucr_offense_code_1 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_2 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_3 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_3 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_4 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_5 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_6 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_7 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_8 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_9 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_10 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter'))
################ batch header
batch14 <- readRDS("./nibrs_batch_headers_rds/nibrs_batch_header_2014.rds")
########## arrestee segment
arrestee14 <- readRDS("./nibrs_1991_2020_arrestee_segment_rds/nibrs_arrestee_segment_2014.rds")
################ merge
merge1_14 <- merge(x=vic14, y=offense14, by="unique_incident_id", all.x=TRUE) # merge victim (index) and offense
batch14 <- rename(batch14, ori.x=ori) #rename batch for facilitating merging
merge2_14 <- merge(x=merge1_14, y=batch14, by="ori.x", all.x=TRUE) # merge previous, and batch
merge_final_14 <- merge(x=merge2_14, y=adm14, by="unique_incident_id", all.x=TRUE) # merge previous, and administrative
merge_final_14 <- merge(x=merge_final_14, y=arrestee14, by="unique_incident_id", all.x=TRUE)
#create Solved variable
merge_final_14$Solved <- ifelse(merge_final_14$total_arrestee_segments>0 | merge_final_14$cleared_exceptionally!="not applicable", 1, 0)
# remove to free up space in memory
rm(adm14, batch14, arrestee14)


##################################################################################################
##################################################################################################
##################################################################################################
##########################################   2015   ##############################################
##################################################################################################
##################################################################################################
##################################################################################################


################ offense segment
offense15 <- readRDS("./nibrs_1991_2020_offense_segment_rds/nibrs_offense_segment_2015.rds")
offense15 <- filter(offense15, ucr_offense_code %in% c('justifiable homicide', 
                                                       'murder/nonnegligent manslaughter', 
                                                       'negligent manslaughter')) # filter offense
################ administrative segment
adm15 <- readRDS("./nibrs_1991_2020_administrative_segment_rds/nibrs_administrative_segment_2015.rds")
################ victim segment
vic15 <- readRDS("./nibrs_1991_2020_victim_segment_rds/nibrs_victim_segment_2015.rds")
vic15 <- filter(vic15, ucr_offense_code_1 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_2 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_3 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_3 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_4 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_5 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_6 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_7 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_8 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_9 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_10 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter'))
################ batch header
batch15 <- readRDS("./nibrs_batch_headers_rds/nibrs_batch_header_2015.rds")
########## arrestee segment
arrestee15 <- readRDS("./nibrs_1991_2020_arrestee_segment_rds/nibrs_arrestee_segment_2015.rds")
################ merge
merge1_15 <- merge(x=vic15, y=offense15, by="unique_incident_id", all.x=TRUE) # merge victim (index) and offense
batch15 <- rename(batch15, ori.x=ori) #rename batch for facilitating merging
merge2_15 <- merge(x=merge1_15, y=batch15, by="ori.x", all.x=TRUE) # merge previous, and batch
merge_final_15 <- merge(x=merge2_15, y=adm15, by="unique_incident_id", all.x=TRUE) # merge previous, and administrative
merge_final_15 <- merge(x=merge_final_15, y=arrestee15, by="unique_incident_id", all.x=TRUE)
#create Solved variable
merge_final_15$Solved <- ifelse(merge_final_15$total_arrestee_segments>0 | merge_final_15$cleared_exceptionally!="not applicable", 1, 0)
# remove to free up space in memory
rm(adm15, batch15, arrestee15)


##################################################################################################
##################################################################################################
##################################################################################################
##########################################   2016   ##############################################
##################################################################################################
##################################################################################################
##################################################################################################


################ offense segment
offense16 <- readRDS("./nibrs_1991_2020_offense_segment_rds/nibrs_offense_segment_2016.rds")
offense16 <- filter(offense16, ucr_offense_code %in% c('justifiable homicide', 
                                                       'murder/nonnegligent manslaughter', 
                                                       'negligent manslaughter')) # filter offense
################ administrative segment
adm16 <- readRDS("./nibrs_1991_2020_administrative_segment_rds/nibrs_administrative_segment_2016.rds")
################ victim segment
vic16 <- readRDS("./nibrs_1991_2020_victim_segment_rds/nibrs_victim_segment_2016.rds")
vic16 <- filter(vic16, ucr_offense_code_1 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_2 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_3 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_3 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_4 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_5 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_6 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_7 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_8 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_9 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_10 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter'))
################ batch header
batch16 <- readRDS("./nibrs_batch_headers_rds/nibrs_batch_header_2016.rds")
########## arrestee segment
arrestee16 <- readRDS("./nibrs_1991_2020_arrestee_segment_rds/nibrs_arrestee_segment_2016.rds")
################ merge
merge1_16 <- merge(x=vic16, y=offense16, by="unique_incident_id", all.x=TRUE) # merge victim (index) and offense
batch16 <- rename(batch16, ori.x=ori) #rename batch for facilitating merging
merge2_16 <- merge(x=merge1_16, y=batch16, by="ori.x", all.x=TRUE) # merge previous, and batch
merge_final_16 <- merge(x=merge2_16, y=adm16, by="unique_incident_id", all.x=TRUE) # merge previous, and administrative
merge_final_16 <- merge(x=merge_final_16, y=arrestee16, by="unique_incident_id", all.x=TRUE)
#create Solved variable
merge_final_16$Solved <- ifelse(merge_final_16$total_arrestee_segments>0 | merge_final_16$cleared_exceptionally!="not applicable", 1, 0)
# remove to free up space in memory
rm(adm16, batch16, arrestee16)


##################################################################################################
##################################################################################################
##################################################################################################
##########################################   2017   ##############################################
##################################################################################################
##################################################################################################
##################################################################################################


################ offense segment
offense17 <- readRDS("./nibrs_1991_2020_offense_segment_rds/nibrs_offense_segment_2017.rds")
offense17 <- filter(offense17, ucr_offense_code %in% c('justifiable homicide', 
                                                       'murder/nonnegligent manslaughter', 
                                                       'negligent manslaughter')) # filter offense
################ administrative segment
adm17 <- readRDS("./nibrs_1991_2020_administrative_segment_rds/nibrs_administrative_segment_2017.rds")
################ victim segment
vic17 <- readRDS("./nibrs_1991_2020_victim_segment_rds/nibrs_victim_segment_2017.rds")
vic17 <- filter(vic17, ucr_offense_code_1 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_2 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_3 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_3 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_4 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_5 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_6 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_7 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_8 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_9 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_10 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter'))
################ batch header
batch17 <- readRDS("./nibrs_batch_headers_rds/nibrs_batch_header_2017.rds")
########## arrestee segment
arrestee17 <- readRDS("./nibrs_1991_2020_arrestee_segment_rds/nibrs_arrestee_segment_2017.rds")
################ merge
merge1_17 <- merge(x=vic17, y=offense17, by="unique_incident_id", all.x=TRUE) # merge victim (index) and offense
batch17 <- rename(batch17, ori.x=ori) #rename batch for facilitating merging
merge2_17 <- merge(x=merge1_17, y=batch17, by="ori.x", all.x=TRUE) # merge previous, and batch
merge_final_17 <- merge(x=merge2_17, y=adm17, by="unique_incident_id", all.x=TRUE) # merge previous, and administrative
merge_final_17 <- merge(x=merge_final_17, y=arrestee17, by="unique_incident_id", all.x=TRUE)
#create Solved variable
merge_final_17$Solved <- ifelse(merge_final_17$total_arrestee_segments>0 | merge_final_17$cleared_exceptionally!="not applicable", 1, 0)
# remove to free up space in memory
rm(adm17, batch17, arrestee17)

##################################################################################################
##################################################################################################
##################################################################################################
##########################################   2018   ##############################################
##################################################################################################
##################################################################################################
##################################################################################################


################ offense segment
offense18 <- readRDS("./nibrs_1991_2020_offense_segment_rds/nibrs_offense_segment_2018.rds")
offense18 <- filter(offense18, ucr_offense_code %in% c('justifiable homicide', 
                                                       'murder/nonnegligent manslaughter', 
                                                       'negligent manslaughter')) # filter offense
################ administrative segment
adm18 <- readRDS("./nibrs_1991_2020_administrative_segment_rds/nibrs_administrative_segment_2018.rds")
################ victim segment
vic18 <- readRDS("./nibrs_1991_2020_victim_segment_rds/nibrs_victim_segment_2018.rds")
vic18 <- filter(vic18, ucr_offense_code_1 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_2 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_3 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_3 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_4 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_5 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_6 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_7 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_8 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_9 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_10 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter'))
################ batch header
batch18 <- readRDS("./nibrs_batch_headers_rds/nibrs_batch_header_2018.rds")
########## arrestee segment
arrestee18 <- readRDS("./nibrs_1991_2020_arrestee_segment_rds/nibrs_arrestee_segment_2018.rds")
################ merge
merge1_18 <- merge(x=vic18, y=offense18, by="unique_incident_id", all.x=TRUE) # merge victim (index) and offense
batch18 <- rename(batch18, ori.x=ori) #rename batch for facilitating merging
merge2_18 <- merge(x=merge1_18, y=batch18, by="ori.x", all.x=TRUE) # merge previous, and batch
merge_final_18 <- merge(x=merge2_18, y=adm18, by="unique_incident_id", all.x=TRUE) # merge previous, and administrative
merge_final_18 <- merge(x=merge_final_18, y=arrestee18, by="unique_incident_id", all.x=TRUE)
#create Solved variable
merge_final_18$Solved <- ifelse(merge_final_18$total_arrestee_segments>0 | merge_final_18$cleared_exceptionally!="not applicable", 1, 0)
# remove to free up space in memory
rm(adm18, batch18, arrestee18)


##################################################################################################
##################################################################################################
##################################################################################################
##########################################   2019   ##############################################
##################################################################################################
##################################################################################################
##################################################################################################


################ offense segment
offense19 <- readRDS("./nibrs_1991_2020_offense_segment_rds/nibrs_offense_segment_2019.rds")
offense19 <- filter(offense19, ucr_offense_code %in% c('justifiable homicide', 
                                                       'murder/nonnegligent manslaughter', 
                                                       'negligent manslaughter')) # filter offense
################ administrative segment
adm19 <- readRDS("./nibrs_1991_2020_administrative_segment_rds/nibrs_administrative_segment_2019.rds")
################ victim segment
vic19 <- readRDS("./nibrs_1991_2020_victim_segment_rds/nibrs_victim_segment_2019.rds")
vic19 <- filter(vic19, ucr_offense_code_1 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_2 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_3 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_3 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_4 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_5 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_6 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_7 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_8 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_9 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_10 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter'))
################ batch header
batch19 <- readRDS("./nibrs_batch_headers_rds/nibrs_batch_header_2019.rds")
########## arrestee segment
arrestee19 <- readRDS("./nibrs_1991_2020_arrestee_segment_rds/nibrs_arrestee_segment_2019.rds")
################ merge
merge1_19 <- merge(x=vic19, y=offense19, by="unique_incident_id", all.x=TRUE) # merge victim (index) and offense
batch19 <- rename(batch19, ori.x=ori) #rename batch for facilitating merging
merge2_19 <- merge(x=merge1_19, y=batch19, by="ori.x", all.x=TRUE) # merge previous, and batch
merge_final_19 <- merge(x=merge2_19, y=adm19, by="unique_incident_id", all.x=TRUE) # merge previous, and administrative
merge_final_19 <- merge(x=merge_final_19, y=arrestee19, by="unique_incident_id", all.x=TRUE)
#create Solved variable
merge_final_19$Solved <- ifelse(merge_final_19$total_arrestee_segments>0 | merge_final_19$cleared_exceptionally!="not applicable", 1, 0)
# remove to free up space in memory
rm(adm19, batch19, arrestee19)


##################################################################################################
##################################################################################################
##################################################################################################
##########################################   2020   ##############################################
##################################################################################################
##################################################################################################
##################################################################################################


################ offense segment
offense20 <- readRDS("./nibrs_1991_2020_offense_segment_rds/nibrs_offense_segment_2020.rds")
offense20 <- filter(offense20, ucr_offense_code %in% c('justifiable homicide', 
                                                       'murder/nonnegligent manslaughter', 
                                                       'negligent manslaughter')) # filter offense
################ administrative segment
adm20 <- readRDS("./nibrs_1991_2020_administrative_segment_rds/nibrs_administrative_segment_2020.rds")
################ victim segment
vic20 <- readRDS("./nibrs_1991_2020_victim_segment_rds/nibrs_victim_segment_2020.rds")
vic20 <- filter(vic20, ucr_offense_code_1 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_2 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_3 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_3 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_4 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_5 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_6 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_7 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_8 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_9 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter')|
                  ucr_offense_code_10 %in% c('justifiable homicide', 'murder/nonnegligent manslaughter', 'negligent manslaughter'))
################ batch header
batch20 <- readRDS("./nibrs_batch_headers_rds/nibrs_batch_header_2020.rds")
########## arrestee segment
arrestee20 <- readRDS("./nibrs_1991_2020_arrestee_segment_rds/nibrs_arrestee_segment_2020.rds")
################ merge
merge1_20 <- merge(x=vic20, y=offense20, by="unique_incident_id", all.x=TRUE) # merge victim (index) and offense
batch20 <- rename(batch20, ori.x=ori) #rename batch for facilitating merging
merge2_20 <- merge(x=merge1_20, y=batch20, by="ori.x", all.x=TRUE) # merge previous, and batch
merge_final_20 <- merge(x=merge2_20, y=adm20, by="unique_incident_id", all.x=TRUE) # merge previous, and administrative
merge_final_20 <- merge(x=merge_final_20, y=arrestee20, by="unique_incident_id", all.x=TRUE)
#create Solved variable
merge_final_20$Solved <- ifelse(merge_final_20$total_arrestee_segments>0 | merge_final_20$cleared_exceptionally!="not applicable", 1, 0)
# remove to free up space in memory
rm(adm20, batch20, arrestee20)



# check columns that are present only from 2013 on, and remove to rbind
names20 <- names(merge_final_20)
names10 <- names(merge_final_10)
setdiff(names20, names10)
setdiff(names10, names20)



# change x01_06_12_indicator to capitalized X as after 13
# dat = lapply(before13, function(x){names(x)[which(names(x)=="X01_06_12_indicator")]="x01_06_12_indicator";x}) 
# names(dat) <-c("merge_final_91",
#                "merge_final_92",
#                "merge_final_93",
#                "merge_final_94",
#                "merge_final_95",
#                "merge_final_96",
#                "merge_final_97",
#                "merge_final_98",
#                "merge_final_99",
#                "merge_final_00",
#                "merge_final_01",
#                "merge_final_02",
#                "merge_final_03",
#                "merge_final_04",
#                "merge_final_05",
#                "merge_final_06",
#                "merge_final_07",
#                "merge_final_08",
#                "merge_final_09",
#                "merge_final_10",
#                "merge_final_11",
#                "merge_final_12")
# list2env(dat,.GlobalEnv)


# remove columns that don't match
all_datasets <- list(merge_final_91,
                     merge_final_92,
                     merge_final_93,
                     merge_final_94,
                     merge_final_95,
                     merge_final_96,
                     merge_final_97,
                     merge_final_98,
                     merge_final_99,
                     merge_final_00,
                     merge_final_01,
                     merge_final_02,
                     merge_final_03,
                     merge_final_04,
                     merge_final_05,
                     merge_final_06,
                     merge_final_07,
                     merge_final_08,
                     merge_final_09,
                     merge_final_10,
                     merge_final_11,
                     merge_final_12,
                     merge_final_13,
                     merge_final_14,
                     merge_final_15,
                     merge_final_16,
                     merge_final_17,
                     merge_final_18,
                     merge_final_19,
                     merge_final_20)
common_cols <- Reduce(intersect, lapply(all_datasets, colnames))

# all years together
nibrs <- do.call(rbind, lapply(all_datasets, `[`, common_cols ))
saveRDS(nibrs, file="nibrs91_20_with_delay.rds")

write.csv(nibrs, "nibrs91_20_with_delay.csv")






